""""
Case Type   : D库常用语法兼容-常用函数兼容
Case Name   : 查看sys.all_objects视图视图返回值测试
Create At   : 2025-10-10
Owner       : chen-czywj
Description :
    1.使用兼容D库创建数据库
    2.执行创建存储过程测试
    3.查看视图返回值正确无误
    4.清理环境
Expect      :
    1.成功
    2.成功
    3.查看视图返回值正确无误
    4.清理环境
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from yat.test import Node
from yat.test import macro

log = Logger()
primary_sh = CommonSH('PrimaryDbUser')

class Function_Commonviews_Case0036(unittest.TestCase):
    def setUp(self):
        log.info(f'--------{os.path.basename(__file__)} start--------')
        self.constant = Constant()
        self.dbname_d = Node('PrimaryDbDmode').db_name
        self.dbname = 'test_views_001'
        self.schema_name = 'sys_view_test_01'
        self.re_name = 'r_commonviews_036'
        self.tb_name = 't_commonviews_036'
        self.se_name = 's_commonviews_036'

    def test_Function_Commonviews(self):
        text = '-------------step1: 使用兼容D库创建数据库; expect: 成功-------------'
        log.info(text)
        msg = primary_sh.check_connection_status(self.constant.START_STATUS_MSG)
        self.assertTrue(msg)
        create_db = f'''drop database if exists {self.dbname};
            create database if not exists {self.dbname} dbcompatibility = 'D';'''
        res = primary_sh.execut_db_sql(create_db, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, res)
        load_extension = primary_sh.execut_db_sql('create extension shark;',
                                     dbname=self.dbname)
        log.info(load_extension)
        self.assertIn(self.constant.create_extension_success, load_extension)

        text = '-------------step2: 执行创建存储过程测试; expect: 成功-------------'
        log.info(text)
        create_tb = f'''set search_path  to sys;
create or replace function {self.re_name}(a int, b int) returns int as \$\$
begin
 return a - b;
end;
\$\$ language plpgsql;'''
        res = primary_sh.execut_db_sql(create_tb, dbname=self.dbname)
        log.info(res)
        self.assertIn(self.constant.SET_SUCCESS_MSG, res)
        self.assertIn("Permission denied to create", res)
        create_tb = f'''create schema {self.schema_name};
                set search_path to {self.schema_name};
create or replace function {self.re_name}(a int, b int) returns int as \$\$
begin
 return a - b;
end;
\$\$ language plpgsql;
            create sequence if not exists {self.se_name} increment by 1 start with 1 nocycle;
            create table {self.tb_name} (order_id int primary key default 
                nextval('{self.se_name}'), order_date date);
            select {self.se_name}.nextval;'''
        res = primary_sh.execut_db_sql(create_tb, dbname=self.dbname)
        log.info(res)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, res)
        self.assertIn(self.constant.SET_SUCCESS_MSG, res)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, res)
        self.assertIn(self.constant.CREATE_SEQUENCE_SUCCESS_MSG, res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res)
        self.assertIn('       1', res)

        text = '-------------step3: 查看视图返回值正确无误; expect: 返回值正确-------------'
        log.info(text)
        select_sql = f"""select name, s.nspname, type, type_desc, is_ms_shipped, 
            is_published, is_schema_published,principal_id,create_date
            from sys.all_objects o
            inner join pg_namespace s on o.schema_id = s.oid
            left join pg_class po on po.oid = parent_object_id
            where s.nspname = '{self.schema_name}'
            order by object_id desc limit 50;"""
        res = primary_sh.execut_db_sql(select_sql, dbname=self.dbname)
        log.info(res)
        self.assertIn(r'r_commonviews_036      | sys_view_test_01 | FN  ' \
            r' | SQL_SCALAR_FUNCTION', res)
        self.assertIn(r's_commonviews_036      | sys_view_test_01 | SO  ' \
            r' | SEQUENCE_OBJECT ', res)

    def tearDown(self):
        text = '------------------step4: 清理环境; expect: 成功------------------'
        log.info(text)
        drop_db = f'''set search_path to {self.schema_name};
            drop function {self.re_name};
            drop table {self.tb_name};
            drop sequence {self.se_name};
            '''
        res = primary_sh.execut_db_sql(drop_db, dbname=self.dbname)
        log.info(res)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res)
        self.assertIn(self.constant.DROP_SEQUENCE_SUCCESS_MSG, res)
        drop_db = f'''drop schema if exists {self.schema_name};'''
        res = primary_sh.execut_db_sql(drop_db, dbname=self.dbname)
        log.info(res)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG, res)
        drop_db = f'''
            drop database if exists {self.dbname};'''
        res = primary_sh.execut_db_sql(drop_db, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res)
